{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "### Import Portfolios from Excel\n",
    "\n",
    "This example will demonstrate how to create a `Portfolio` from an excel file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "from gs_quant.session import Environment, GsSession\n",
    "GsSession.use(Environment.PROD, client_id=None, client_secret=None, scopes=('run_analytics',))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's assume you have an excel file with many different trades on which you would like to compute `gs-quant` analytics.\n",
    "You can import you file into a pandas dataframe with the below command: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# import pandas as pd\n",
    "# data = pd.read_excel(r'path_to_my_file.csv', sheet_name='my_sheet', usecols=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "For the benefit of the analysis here I simulated a dummy dataframe representing the result of your file import."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "data = pd.DataFrame.from_dict({'name': {0: 'my favourite swap',\n",
    "  1: 'my favourite swaption',\n",
    "  2: None,\n",
    "  3: None,\n",
    "  4: None,\n",
    "  5: None},\n",
    " 'trade_type': {0: 'Swap',\n",
    "  1: 'Swaption',\n",
    "  2: 'Swaption',\n",
    "  3: 'Swaption',\n",
    "  4: 'Swaption',\n",
    "  5: 'Swaption'},\n",
    " 'rate': {0: 0.01, 1: None, 2: None, 3: None, 4: None, 5: None},\n",
    " 'strike': {0: None, 1: '2%', 2: '0.02', 3: '0.02', 4: '0.02', 5: '0.02'},\n",
    " 'ccy': {0: 'EUR', 1: 'GBP', 2: 'GBP', 3: 'GBP', 4: 'GBP', 5: 'GBP'},\n",
    " 'freq': {0: '3m/6m',\n",
    "  1: '3m/6m',\n",
    "  2: '3m/6m',\n",
    "  3: '3m/6m',\n",
    "  4: '3m/6m',\n",
    "  5: '3m/6m'},\n",
    " 'index': {0: 'EURIBOR-TELERATE',\n",
    "  1: 'LIBOR-BBA',\n",
    "  2: 'LIBOR-BBA',\n",
    "  3: 'LIBOR-BBA',\n",
    "  4: 'LIBOR-BBA',\n",
    "  5: 'LIBOR-BBA'},\n",
    " 'expiration_date': {0: '30/06/2021',\n",
    "  1: '30/06/2021',\n",
    "  2: '3d',\n",
    "  3: '30/06/2021',\n",
    "  4: '30/06/2021',\n",
    "  5: '3m'},\n",
    " 'asset_class': {0: 'rates',\n",
    "  1: 'rates',\n",
    "  2: 'rates',\n",
    "  3: 'rates',\n",
    "  4: 'rates',\n",
    "  5: 'rates'},\n",
    " })"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "In order to support various excel files and formats, the `from_frame` function takes a mapping argument which enables you \n",
    "to specify which columns of your excel file correspond to the associated `gs_quant` instrument attribute. \n",
    "You may also specify date formats to expand the list of the ones supported by default."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "from gs_quant.markets.portfolio import Portfolio\n",
    "\n",
    "mapper = {\n",
    "    'type' : 'trade_type',\n",
    "    'fixed_rate': 'rate',\n",
    "    'pay_ccy': 'ccy',\n",
    "    'fixed_rate_frequency': lambda row: row['freq'][:row['freq'].index(\"/\")],\n",
    "    'floating_rate_frequency': lambda row: row['freq'][row['freq'].index(\"/\")+1:],\n",
    "    'floating_rate_option': lambda row: row['ccy']+'-'+row['index'],\n",
    "}\n",
    "\n",
    "portfolio = Portfolio.from_frame(data, mappings=mapper)\n",
    "portfolio.to_frame().reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "If you excel file is in csv format, you may also use the `from_csv` command which executes the two above steps \n",
    "all together and converts your csv to a `Portfolio` directly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# portfolio = Portfolio.from_csv(r'path_to_my_file.csv', mappings=mapper)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "You can now leverage all the risk and pricing functionality of `gs-quant` on your Excel built portfolio!"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  },
  "pycharm": {
   "stem_cell": {
    "cell_type": "raw",
    "source": [],
    "metadata": {
     "collapsed": false
    }
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}